package com.apes.fn.server.productReceive;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.stock.model.Location;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface ProductReceiveItemRepository extends CustomRepository<ProductReceiveItem, String> {

    /***
     * 查询当前仓库下所有能归还的商品
     * @return
     */

    @Query("select T from ProductReceiveItem T where T.productReceive.location = :location and  T.qtyReceive -  T.qtyReturned > 0 and T.productReceive.state = 'approve' and T.servicePlanItem.servicePlan.state not in ('qualityTest', 'receivables')")
    List<ProductReceiveItem> findAllReturnable(@Param("location") Location location);

    /***
     * 查询当前单据下所有能归还的商品
     * @return
     */

    @Query("select T from ProductReceiveItem T where T.productReceive.location = :location and  T.qtyReceive -  T.qtyReturned > 0 and T.productReceive.state = 'approve' and T.servicePlanItem.servicePlan.state  in ( 'qualityTest')and T.servicePlanItem.servicePlan.id =:planId")
    List<ProductReceiveItem> findAllReturnableByPlanId(@Param("location") Location location,@Param("planId") String planId);

    @Query("select T from ProductReceiveItem T where T.productReceive.location.depot.dept.id = :deptId and  T.qtyReceive -  T.qtyReturned > 0 and T.productReceive.state = 'approve' and T.servicePlanItem.servicePlan.state not in ('qualityTest', 'receivables')")
    List<ProductReceiveItem> findAllReturn(@Param("deptId") String deptId);

    /**
     * fetch data by local Permit id and id
     *
     * @param localPermitId 本地权限id
     * @param id id
     * @return List<ProductReceive>
     */
    @Query("select T from ProductReceiveItem T " +
            "where T.productReceive.id=:id and T.productReceive.state = 'approve' and T.servicePlanItem.servicePlan.state not in ('qualityTest', 'receivables')" +
            "and exists (select 1 from LocalPermitItem L where L.type.id='pt_location'  and T.productReceive.location.id =L.orgId and L.localPermit.id =:localPermitId)")
    List<ProductReceiveItem> findAllByLocalPermitIdAndId(@Param("localPermitId") String localPermitId, @Param("id") String id);

    /**
     * 根据服务方案明细编码查询领用单号
     *
     * @param servicePlanItemId 服务方案明细ID
     * @return List<ProductReceiveItem>
     */
    @Query("select T from ProductReceiveItem T where T.qtyReceive - T.qtyReturned > 0 " +
            "and T.productReceive.state = 'approve' and T.servicePlanItem.id = :servicePlanItemId ")
    List<ProductReceiveItem> findAllByServicePlanItemId(@Param("servicePlanItemId") String servicePlanItemId);

    @Query(value = "SELECT A.* FROM STOCK_PRODUCT_RECEIVE_ITEM A WHERE A.SERVICE_PLAN_ITEM_ID = :servicePlanItemId AND A.CREATE_DATE = " +
            "(SELECT MAX(T.CREATE_DATE) FROM STOCK_PRODUCT_RECEIVE B, STOCK_PRODUCT_RECEIVE_ITEM T " +
            "WHERE B.ID = T.PRODUCT_RECEIVE_ID AND B.STATE = 'approve' AND T.SERVICE_PLAN_ITEM_ID = :servicePlanItemId)",nativeQuery = true)
    ProductReceiveItem findMaxByServicePlanItemId(@Param("servicePlanItemId") String servicePlanItemId);
}
